package com.monitor.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.monitor.entity.ErpInspectionContent;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author LX
 * @since 2018-09-30
 */
public interface ErpInspectionContentMapper extends BaseMapper<ErpInspectionContent> {

    @Select("select max(id) from erp_inspection_content  where customer_id=#{plate}")
    Integer getMaxIdContent(@Param("plate") String plate);

    @Select("select max(id) from erp_inspection_content  where customer_id=#{plate}  and (dt>=#{startDate}  and dt<=#{endDate}) or dt=null ")
    Integer getMaxIdContentByPro(@Param("plate") String plate,@Param("startDate")String startDate,@Param("endDate")String endDate);

    @Select("SELECT m.id as mId,h.inspection_dt as inspectionDt,i.dt as dt,i.deep_lines_1 as deepLines1,i.deep_lines_2 as deepLines2,i.deep_lines_3 as deepLines3,i.deep_lines_4 as deepLines4 from erp_inspection_content i, erp_inspection_order h, erp_materiel m 	where i.form_id=h.form_id  and i.brand=m.materiel_brand	and i.specification=m.materiel_Specifications 	and i.size=m.materiel_model	and i.produce_id=m.materiel_production_id	and h.inspection_dt>=#{startTime} 	and h.inspection_dt<=#{endTime} 	and m.warehouse_id in (${wareId}) order by h.inspection_dt desc ")
    public List<Map<String, Object>> selectByWareIds(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("wareId")String wareId);
    
    @Select("SELECT * FROM erp_inspection_content I WHERE I.form_id IN (SELECT IH.form_id FROM erp_inspection_order IH WHERE IH.inspection_dt>=#{startTime} AND IH.inspection_dt<=#{endTime} AND IH.branch=#{branch})")
    public List<ErpInspectionContent> selectByBranch(@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("branch")String branch);
    
    @Select("select s.customer_id from erp_inspection_content s where s.form_id in (select p.form_id from erp_inspection_order p where p.branch =#{branch} and  p.inspection_dt >= #{startTime} and p.inspection_dt <= #{endTime}  ) group by s.customer_id")
    public List<String> selectByBranchAndTime(@Param("branch")String branch,@Param("startTime")String startTime,@Param("endTime")String endTime);
    
    @Select("SELECT insh.mileage from erp_inspection_content ins, erp_inspection_order insh where ins.form_id=insh.form_id and ins.customer_id=#{custId} and insh.inspection_dt>=#{startTime} and insh.inspection_dt<=#{endTime} order by insh.mileage desc")
    public List<Map<String, Object>> selectByCustIdAndTime(@Param("custId")String custId,@Param("startTime")String startTime,@Param("endTime")String endTime);
    
    @Select("select jc.* from erp_inspection_content jc, erp_inspection_order jo WHERE jc.form_id=jo.form_id and jc.customer_id=#{custId} and jo.inspection_dt>=#{startTime} AND jo.inspection_dt<=#{endTime} order by jo.inspection_dt desc")
    public List<ErpInspectionContent> selectByCustId(@Param("custId")String custId,@Param("startTime")String startTime,@Param("endTime")String endTime);
    
    @Select("SELECT * FROM erp_inspection_content ic WHERE ic.customer_id=#{plate} and ic.form_id is null order by ic.id limit #{page},#{limit}")
    public List<ErpInspectionContent> selectByPlate(@Param("plate") String plate,@Param("page")int page,@Param("limit")int limit);
    
    @Select("SELECT * FROM erp_inspection_content as ipc LEFT JOIN erp_inspection_order as io ON ipc.pid=io.id WHERE ipc.produce_id=#{produceId} order by io.inspection_dt asc")
    public List<Map<String, Object>> selectByProduceId(@Param("produceId") String produceId);
}
